﻿-- Création de la vue [dbo].[vw_medianamik_ModifiedNode]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_ModifiedNode]'))
BEGIN
	DECLARE @q NVARCHAR(MAX)
    SELECT @q = 'CREATE VIEW [dbo].[vw_medianamik_ModifiedNode] AS 
WITH NullRevisions AS (SELECT     NodeId, CultureName
                         FROM          dbo.medianamik_NodePropertyValue AS npv
                         WHERE      (Revision IS NULL)
                         GROUP BY NodeId, CultureName)
, NodeCulture AS (Select ni.NodeId, ni.CultureName
				FROM dbo.medianamik_NodeInstance as ni
				INNER JOIN NullRevisions nr ON nr.NodeId = ni.NodeId AND nr.CultureName IN (ni.CultureName, '''')
				GROUP BY ni.NodeId, ni.CultureName)
SELECT  n.*, nc.CultureName, t.path as TypePath    FROM NodeCulture nc
inner join medianamik_node n on nc.nodeid = n.nodeid
inner join medianamik_type t on t.typeid = n.typeid
where n.isdeleted = 0
'
	EXEC sp_executesql @q
END
ELSE
BEGIN
	DECLARE @q2 NVARCHAR(MAX)
		SELECT @q2 = 'ALTER VIEW [dbo].[vw_medianamik_ModifiedNode] AS 
	WITH NullRevisions AS (SELECT     NodeId, CultureName
							 FROM          dbo.medianamik_NodePropertyValue AS npv
							 WHERE      (Revision IS NULL)
							 GROUP BY NodeId, CultureName)
	, NodeCulture AS (Select ni.NodeId, ni.CultureName
					FROM dbo.medianamik_NodeInstance as ni
					INNER JOIN NullRevisions nr ON nr.NodeId = ni.NodeId AND nr.CultureName IN (ni.CultureName, '''')
					GROUP BY ni.NodeId, ni.CultureName)
	SELECT  n.*, nc.CultureName,  t.path as TypePath    FROM NodeCulture nc
	inner join medianamik_node n on nc.nodeid = n.nodeid
	inner join medianamik_type t on t.typeid = n.typeid
	where n.isdeleted = 0
	'
	EXEC sp_executesql @q2
END

-- Création d'un index sur la colonne Revision de NodePropertyValue
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_NodePropertyValue]') AND name = N'IX_medianamik_NodePropertyValue_i_Revision_o_NodeId_CultureName')
BEGIN
	CREATE NONCLUSTERED INDEX [IX_medianamik_NodePropertyValue_i_Revision_o_NodeId_CultureName] ON [dbo].[medianamik_NodePropertyValue] 
	(
		[Revision] ASC
	)
	INCLUDE ( [NodeId],[CultureName])
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END
